package com.tbynet.jwp.repository.impl;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.tbynet.jwp.framework.aop.Aop;
import com.tbynet.jwp.framework.core._JwpServiceProvider;
import com.tbynet.jwp.model.Comments;
import com.tbynet.jwp.repository.spi.CommentService;

import java.util.*;

/**
 * 评论服务实现类
 * @ClassName: CommentServiceProvider
 * @Description: 负责评论数据的获取、添加、修改、删除功能的具体实现，职责单一
 * @Author: 佰亿互联
 * @Date: 2025年11月4日
 *
 * @Copyright: 2025 www.tbynet.com Inc. All rights reserved.
 * 注意: 本内容仅限于内部传阅，禁止外泄以及用于其他的商业目
 */
@Aop(CommentService.class)
public class CommentServiceImpl extends _JwpServiceProvider<Comments> implements CommentService {

    @Override
    protected String buildSearchWhereSql(String keyword, List<Object> params) {
        if (keyword != null && !keyword.trim().isEmpty()) {
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");
            return " WHERE (comment_author LIKE ? OR comment_author_email LIKE ? OR comment_author_url LIKE ? OR comment_content LIKE ?)";
        }
        return " WHERE 1=1";
    }

    /**
     * 统计文章评论数量
     * @param postId 文章ID
     * @return 评论数量
     */
    @Override
    public int countByPostId(Object postId) {
        return executeWithResultLogging("countByPostId", () -> {
            validateNotNull(postId, "文章ID不能为空");
            String sql = "SELECT COUNT(*) FROM wp_comments WHERE comment_post_ID = ? AND comment_approved = '1'";
            return Db.queryInt(sql, postId);
        }, postId);
    }

    @Override
    public int countAllByPostId(Object postId) {
        return executeWithResultLogging("countAllByPostId", () -> {
            validateNotNull(postId, "文章ID不能为空");
            String sql = "SELECT COUNT(*) FROM wp_comments WHERE comment_post_ID = ?";
            return Db.queryInt(sql, postId);
        }, postId);
    }

    /**
     * 获取待审核评论数量统计
     * @param postIds 文章ID数组
     * @return 待审核评论统计列表
     */
    @Override
    public List<Record> getPendingCommentsCount(String[] postIds) {
        return executeWithResultLogging("getPendingCommentsCount", () -> {
            if (postIds == null || postIds.length == 0) {
                return new ArrayList<>();
            }

            StringBuilder sql = new StringBuilder();
            sql.append("SELECT comment_post_ID, COUNT(comment_ID) as num_comments ")
                    .append("FROM wp_comments ")
                    .append("WHERE comment_post_ID IN (");

            List<Object> params = new ArrayList<>();
            for (int i = 0; i < postIds.length; i++) {
                if (i > 0) {
                    sql.append(",");
                }
                sql.append("?");
                params.add(postIds[i]);
            }

            sql.append(") AND comment_approved = '0' GROUP BY comment_post_ID");

            return Db.find(sql.toString(), params.toArray());
        }, postIds.length);
    }

    /**
     * 根据状态获取评论数量
     * @param status 评论状态
     * @return 评论数量
     */
    @Override
    public Long getCommentCountByStatus(String status) {
        return executeWithResultLogging("getCommentCountByStatus", () -> {
            validateNotNull(status, "状态不能为空");
            String sql = "SELECT COUNT(*) FROM wp_comments WHERE " + Comments.assembleCommentApprovedClauses(status);
            return Db.queryLong(sql);
        }, status);
    }

    /**
     * 根据状态分页获取评论ID列表
     * @param status 评论状态
     * @param pageNumber 页码
     * @param pageSize 每页大小
     * @return 评论分页数据
     */
    @Override
    public Page<Record> getCommentPageByStatus(String status, int pageNumber, int pageSize) {
        return executeWithResultLogging("getCommentPageByStatus", () -> {
            validateNotNull(status, "状态不能为空");
            String selectSql = "SELECT wp_comments.comment_ID";
            String fromSql = "FROM wp_comments WHERE " + Comments.assembleCommentApprovedClauses(status) +
                    " ORDER BY wp_comments.comment_date_gmt DESC";
            return Db.paginate(pageNumber, pageSize, selectSql, fromSql);
        }, status, pageNumber, pageSize);
    }

    @Override
    public Page<Comments> getCommentsByPostId(Object postId, int pageNumber, int pageSize, String status) {
        return executeWithResultLogging("getCommentsByPostId", () -> {
            validateNotNull(postId, "文章ID不能为空");

            List<Object> params = new ArrayList<>();
            params.add(postId);

            StringBuilder whereSql = new StringBuilder("WHERE comment_post_ID = ?");

            if (status != null && !status.trim().isEmpty()) {
                whereSql.append(" AND ").append(Comments.assembleCommentApprovedClauses(status));
            }

            String select = "SELECT *";
            String from = "FROM wp_comments " + whereSql + " ORDER BY comment_date_gmt DESC";
            return getDao().paginate(pageNumber, pageSize, select, from, params.toArray());
        }, postId, pageNumber, pageSize, status);
    }

    @Override
    public Page<Comments> getCommentsByUserId(Object userId, int pageNumber, int pageSize, String status) {
        return executeWithResultLogging("getCommentsByUserId", () -> {
            validateNotNull(userId, "用户ID不能为空");

            List<Object> params = new ArrayList<>();
            params.add(userId);

            StringBuilder whereSql = new StringBuilder("WHERE user_id = ?");

            if (status != null && !status.trim().isEmpty()) {
                whereSql.append(" AND ").append(Comments.assembleCommentApprovedClauses(status));
            }

            String select = "SELECT *";
            String from = "FROM wp_comments " + whereSql + " ORDER BY comment_date_gmt DESC";
            return getDao().paginate(pageNumber, pageSize, select, from, params.toArray());
        }, userId, pageNumber, pageSize, status);
    }

    @Override
    public Page<Comments> getChildComments(Object parentId, int pageNumber, int pageSize) {
        return executeWithResultLogging("getChildComments", () -> {
            validateNotNull(parentId, "父评论ID不能为空");
            String select = "SELECT *";
            String from = "FROM wp_comments WHERE comment_parent = ? ORDER BY comment_date_gmt ASC";
            return getDao().paginate(pageNumber, pageSize, select, from, parentId);
        }, parentId, pageNumber, pageSize);
    }

    /**
     * 获取评论状态统计
     * @return 按状态分组的评论统计
     */
    @Override
    public List<Record> getCommentStatusStats() {
        return executeWithResultLogging("getCommentStatusStats", () -> {
            String sql = "SELECT comment_approved, COUNT(*) AS total FROM wp_comments GROUP BY comment_approved";
            return Db.find(sql);
        });
    }

    @Override
    public int countByUserId(Object userId) {
        return executeWithResultLogging("countByUserId", () -> {
            validateNotNull(userId, "用户ID不能为空");
            String sql = "SELECT COUNT(*) FROM wp_comments WHERE user_id = ?";
            return Db.queryInt(sql, userId);
        }, userId);
    }

    /**
     * 更新评论状态
     * @param commentId 评论ID
     * @param status 目标状态
     * @return 更新结果
     */
    @Override
    public boolean updateCommentStatus(String commentId, String status) {
        return executeWithBooleanLogging("updateCommentStatus", () -> {
            validateNotNull(commentId, "评论ID不能为空");
            validateNotNull(status, "状态不能为空");
            String sql = "UPDATE wp_comments SET comment_approved = ? WHERE comment_ID = ?";
            return Db.update(sql, status, commentId) > 0;
        }, commentId, status);
    }

    @Override
    public int batchUpdateCommentStatus(String[] commentIds, String status) {
        return executeWithResultLogging("batchUpdateCommentStatus", () -> {
            validateNotNull(commentIds, "评论ID数组不能为空");
            validateNotNull(status, "状态不能为空");

            if (commentIds.length == 0) {
                return 0;
            }

            StringBuilder sql = new StringBuilder("UPDATE wp_comments SET comment_approved = ? WHERE comment_ID IN (");
            List<Object> params = new ArrayList<>();
            params.add(status);

            for (int i = 0; i < commentIds.length; i++) {
                if (i > 0) {
                    sql.append(",");
                }
                sql.append("?");
                params.add(commentIds[i]);
            }
            sql.append(")");

            return Db.update(sql.toString(), params.toArray());
        }, commentIds.length, status);
    }

    @Override
    public boolean approveComment(String commentId) {
        return executeWithBooleanLogging("approveComment", () ->
                updateCommentStatus(commentId, STATUS_APPROVED), commentId);
    }

    @Override
    public boolean markAsSpam(String commentId) {
        return executeWithBooleanLogging("markAsSpam", () ->
                updateCommentStatus(commentId, STATUS_SPAM), commentId);
    }

    @Override
    public boolean moveToTrash(String commentId) {
        return executeWithBooleanLogging("moveToTrash", () ->
                updateCommentStatus(commentId, STATUS_TRASH), commentId);
    }

    @Override
    public boolean restoreFromTrash(String commentId) {
        return executeWithBooleanLogging("restoreFromTrash", () ->
                updateCommentStatus(commentId, STATUS_APPROVED), commentId);
    }

    @Override
    public boolean deletePermanently(String commentId) {
        return executeWithBooleanLogging("deletePermanently", () -> {
            validateNotNull(commentId, "评论ID不能为空");

            // 先删除元数据
            deleteCommentMeta(commentId);

            // 再删除评论
            String sql = "DELETE FROM wp_comments WHERE comment_ID = ?";
            return Db.update(sql, commentId) > 0;
        }, commentId);
    }

    @Override
    public int batchDeletePermanently(String[] commentIds) {
        return executeWithResultLogging("batchDeletePermanently", () -> {
            validateNotNull(commentIds, "评论ID数组不能为空");

            if (commentIds.length == 0) {
                return 0;
            }

            int successCount = 0;
            for (String commentId : commentIds) {
                if (deletePermanently(commentId)) {
                    successCount++;
                }
            }
            return successCount;
        }, commentIds.length);
    }

    /**
     * 删除评论元数据
     * @param commentId 评论ID
     * @return 删除结果
     */
    @Override
    public boolean deleteCommentMeta(String commentId) {
        return executeWithBooleanLogging("deleteCommentMeta", () -> {
            validateNotNull(commentId, "评论ID不能为空");
            String sql = "DELETE FROM wp_commentmeta WHERE comment_id = ?";
            return Db.update(sql, commentId) >= 0; // 删除0条也算成功
        }, commentId);
    }

    @Override
    public List<Record> getCommentMeta(String commentId) {
        return executeWithResultLogging("getCommentMeta", () -> {
            validateNotNull(commentId, "评论ID不能为空");
            String sql = "SELECT * FROM wp_commentmeta WHERE comment_id = ?";
            return Db.find(sql, commentId);
        }, commentId);
    }

    @Override
    public boolean updateCommentMeta(String commentId, String metaKey, Object metaValue) {
        return executeWithBooleanLogging("updateCommentMeta", () -> {
            validateNotNull(commentId, "评论ID不能为空");
            validateNotNull(metaKey, "元数据键不能为空");

            // 先删除已存在的
            String deleteSql = "DELETE FROM wp_commentmeta WHERE comment_id = ? AND meta_key = ?";
            Db.update(deleteSql, commentId, metaKey);

            // 再插入新的
            String insertSql = "INSERT INTO wp_commentmeta (comment_id, meta_key, meta_value) VALUES (?, ?, ?)";
            return Db.update(insertSql, commentId, metaKey, metaValue) > 0;
        }, commentId, metaKey);
    }

    @Override
    public boolean addCommentMeta(String commentId, String metaKey, Object metaValue) {
        return executeWithBooleanLogging("addCommentMeta", () -> {
            validateNotNull(commentId, "评论ID不能为空");
            validateNotNull(metaKey, "元数据键不能为空");

            String sql = "INSERT INTO wp_commentmeta (comment_id, meta_key, meta_value) VALUES (?, ?, ?)";
            return Db.update(sql, commentId, metaKey, metaValue) > 0;
        }, commentId, metaKey);
    }

    /**
     * 获取评论详情（包含文章信息）
     * @param commentId 评论ID
     * @return 评论详情
     */
    @Override
    public Record getCommentDetail(String commentId) {
        return executeWithResultLogging("getCommentDetail", () -> {
            validateNotNull(commentId, "评论ID不能为空");
            String sql = "SELECT c.*, p.post_title " +
                    "FROM wp_comments c " +
                    "LEFT JOIN wp_posts p ON c.comment_post_ID = p.ID " +
                    "WHERE c.comment_ID = ?";
            return Db.findFirst(sql, commentId);
        }, commentId);
    }

    @Override
    public Page<Comments> searchByEmail(String email, int pageNumber, int pageSize) {
        return executeWithResultLogging("searchByEmail", () -> {
            validateNotNull(email, "邮箱不能为空");
            String select = "SELECT *";
            String from = "FROM wp_comments WHERE comment_author_email LIKE ? ORDER BY comment_date_gmt DESC";
            return getDao().paginate(pageNumber, pageSize, select, from, "%" + email + "%");
        }, email, pageNumber, pageSize);
    }

    @Override
    public Page<Comments> searchByIp(String ip, int pageNumber, int pageSize) {
        return executeWithResultLogging("searchByIp", () -> {
            validateNotNull(ip, "IP地址不能为空");
            String select = "SELECT *";
            String from = "FROM wp_comments WHERE comment_author_IP = ? ORDER BY comment_date_gmt DESC";
            return getDao().paginate(pageNumber, pageSize, select, from, ip);
        }, ip, pageNumber, pageSize);
    }

    @Override
    public Page<Comments> searchByUrl(String url, int pageNumber, int pageSize) {
        return executeWithResultLogging("searchByUrl", () -> {
            validateNotNull(url, "URL不能为空");
            String select = "SELECT *";
            String from = "FROM wp_comments WHERE comment_author_url LIKE ? ORDER BY comment_date_gmt DESC";
            return getDao().paginate(pageNumber, pageSize, select, from, "%" + url + "%");
        }, url, pageNumber, pageSize);
    }

    @Override
    public List<Comments> getRecentComments(int limit) {
        return executeWithResultLogging("getRecentComments", () -> {
            validateNotNull(limit, "数量限制不能为空");
            String sql = "SELECT * FROM wp_comments WHERE comment_approved = '1' ORDER BY comment_date_gmt DESC LIMIT ?";
            return getDao().find(sql, limit);
        }, limit);
    }

    @Override
    public List<Record> getMostCommentedPosts(int limit) {
        return executeWithResultLogging("getMostCommentedPosts", () -> {
            validateNotNull(limit, "数量限制不能为空");
            String sql = "SELECT p.ID, p.post_title, COUNT(c.comment_ID) as comment_count " +
                    "FROM wp_posts p " +
                    "INNER JOIN wp_comments c ON p.ID = c.comment_post_ID " +
                    "WHERE c.comment_approved = '1' " +
                    "GROUP BY p.ID, p.post_title " +
                    "ORDER BY comment_count DESC " +
                    "LIMIT ?";
            return Db.find(sql, limit);
        }, limit);
    }

    @Override
    public boolean isDuplicateComment(Comments comment) {
        return executeWithBooleanLogging("isDuplicateComment", () -> {
            validateNotNull(comment, "评论对象不能为空");

            String sql = "SELECT COUNT(*) FROM wp_comments WHERE " +
                    "comment_author = ? AND comment_author_email = ? AND comment_content = ? AND " +
                    "comment_date_gmt > DATE_SUB(NOW(), INTERVAL 30 MINUTE)";

            Long count = Db.queryLong(sql,
                    comment.getCommentAuthor(),
                    comment.getCommentAuthorEmail(),
                    comment.getCommentContent());

            return count != null && count > 0;
        }, comment);
    }

    @Override
    public boolean validateComment(Comments comment) {
        return executeWithBooleanLogging("validateComment", () -> {
            validateNotNull(comment, "评论对象不能为空");

            // 检查必要字段
            if (comment.getCommentContent() == null || comment.getCommentContent().trim().isEmpty()) {
                return false;
            }

            if (comment.getCommentAuthor() == null || comment.getCommentAuthor().trim().isEmpty()) {
                return false;
            }

            if (comment.getCommentAuthorEmail() == null || comment.getCommentAuthorEmail().trim().isEmpty()) {
                return false;
            }

            // 检查邮箱格式
            String email = comment.getCommentAuthorEmail();
            if (!email.matches("^[A-Za-z0-9+_.-]+@(.+)$")) {
                return false;
            }

            return true;
        }, comment);
    }

    @Override
    public boolean hasPendingComments(String authorEmail, String authorIp) {
        return executeWithBooleanLogging("hasPendingComments", () -> {
            validateNotNull(authorEmail, "作者邮箱不能为空");
            validateNotNull(authorIp, "作者IP不能为空");

            String sql = "SELECT COUNT(*) FROM wp_comments WHERE " +
                    "(comment_author_email = ? OR comment_author_IP = ?) AND comment_approved = '0'";

            Long count = Db.queryLong(sql, authorEmail, authorIp);
            return count != null && count > 0;
        }, authorEmail, authorIp);
    }

    @Override
    public String getModerationKey(String commentId) {
        return executeWithResultLogging("getModerationKey", () -> {
            validateNotNull(commentId, "评论ID不能为空");

            // 生成基于评论ID和日期的哈希值作为审核密钥
            String baseString = commentId + "_" + System.currentTimeMillis();
            return Integer.toHexString(baseString.hashCode());
        }, commentId);
    }

    @Override
    public boolean approveByModerationKey(String moderationKey) {
        return executeWithBooleanLogging("approveByModerationKey", () -> {
            validateNotNull(moderationKey, "审核密钥不能为空");
            // 在实际实现中，需要根据密钥找到对应的评论ID，这里简化处理
            // 实际应该从某个存储中根据密钥获取评论ID
            return false; // 简化实现
        }, moderationKey);
    }

    @Override
    public List<Comments> getCommentThread(Object commentId) {
        return executeWithResultLogging("getCommentThread", () -> {
            validateNotNull(commentId, "评论ID不能为空");

            // 使用递归CTE获取评论线程（如果数据库支持）
            // 这里使用简化实现，获取指定深度的子评论
            String sql = "WITH RECURSIVE comment_thread AS (" +
                    "  SELECT * FROM wp_comments WHERE comment_ID = ? " +
                    "  UNION ALL " +
                    "  SELECT c.* FROM wp_comments c " +
                    "  INNER JOIN comment_thread ct ON c.comment_parent = ct.comment_ID " +
                    ") SELECT * FROM comment_thread ORDER BY comment_date_gmt ASC";

            return getDao().find(sql, commentId);
        }, commentId);
    }

    @Override
    public int getCommentDepth(Object commentId) {
        return executeWithResultLogging("getCommentDepth", () -> {
            validateNotNull(commentId, "评论ID不能为空");

            String sql = "WITH RECURSIVE comment_chain AS (" +
                    "  SELECT comment_ID, comment_parent, 1 as depth FROM wp_comments WHERE comment_ID = ? " +
                    "  UNION ALL " +
                    "  SELECT c.comment_ID, c.comment_parent, cc.depth + 1 " +
                    "  FROM wp_comments c " +
                    "  INNER JOIN comment_chain cc ON c.comment_parent = cc.comment_ID " +
                    ") SELECT MAX(depth) as max_depth FROM comment_chain";

            Integer depth = Db.queryInt(sql, commentId);
            return depth != null ? depth : 1;
        }, commentId);
    }

    @Override
    public List<Comments> getCommentTree(Object postId) {
        return executeWithResultLogging("getCommentTree", () -> {
            validateNotNull(postId, "文章ID不能为空");

            // 获取文章的所有评论，按层级和日期排序
            String sql = "SELECT * FROM wp_comments WHERE comment_post_ID = ? AND comment_approved = '1' " +
                    "ORDER BY comment_parent ASC, comment_date_gmt ASC";

            return getDao().find(sql, postId);
        }, postId);
    }

    @Override
    public int cleanSpamComments(int daysAgo) {
        return executeWithResultLogging("cleanSpamComments", () -> {
            validateNotNull(daysAgo, "天数不能为空");

            String sql = "DELETE FROM wp_comments WHERE comment_approved = 'spam' AND " +
                    "comment_date_gmt < DATE_SUB(NOW(), INTERVAL ? DAY)";

            return Db.update(sql, daysAgo);
        }, daysAgo);
    }

    @Override
    public int emptyTrash() {
        return executeWithResultLogging("emptyTrash", () -> {
            // 先删除回收站中评论的元数据
            String metaSql = "DELETE cm FROM wp_commentmeta cm " +
                    "INNER JOIN wp_comments c ON cm.comment_id = c.comment_ID " +
                    "WHERE c.comment_approved = 'trash'";
            Db.update(metaSql);

            // 再删除回收站中的评论
            String commentSql = "DELETE FROM wp_comments WHERE comment_approved = 'trash'";
            return Db.update(commentSql);
        });
    }

    @Override
    public boolean recalculateCommentCount(Object postId) {
        return executeWithBooleanLogging("recalculateCommentCount", () -> {
            validateNotNull(postId, "文章ID不能为空");

            // 计算已批准的评论数量
            String countSql = "SELECT COUNT(*) FROM wp_comments WHERE comment_post_ID = ? AND comment_approved = '1'";
            Integer count = Db.queryInt(countSql, postId);

            if (count != null) {
                // 更新文章的评论数量
                String updateSql = "UPDATE wp_posts SET comment_count = ? WHERE ID = ?";
                return Db.update(updateSql, count, postId) > 0;
            }

            return false;
        }, postId);
    }

    @Override
    public int batchRecalculateCommentCount(String[] postIds) {
        return executeWithResultLogging("batchRecalculateCommentCount", () -> {
            validateNotNull(postIds, "文章ID数组不能为空");

            int successCount = 0;
            for (String postId : postIds) {
                if (recalculateCommentCount(postId)) {
                    successCount++;
                }
            }
            return successCount;
        }, postIds.length);
    }

    @Override
    public List<Record> exportComments(Map<String, Object> conditions) {
        return executeWithResultLogging("exportComments", () -> {
            List<Object> params = new ArrayList<>();
            StringBuilder sql = new StringBuilder();

            sql.append("SELECT c.*, p.post_title, u.user_login, u.display_name ")
                    .append("FROM wp_comments c ")
                    .append("LEFT JOIN wp_posts p ON c.comment_post_ID = p.ID ")
                    .append("LEFT JOIN wp_users u ON c.user_id = u.ID ")
                    .append("WHERE 1=1");

            // 构建条件
            if (conditions != null && !conditions.isEmpty()) {
                if (conditions.containsKey("status")) {
                    sql.append(" AND ").append(Comments.assembleCommentApprovedClauses(conditions.get("status").toString()));
                }

                if (conditions.containsKey("postId")) {
                    sql.append(" AND c.comment_post_ID = ?");
                    params.add(conditions.get("postId"));
                }

                if (conditions.containsKey("startDate")) {
                    sql.append(" AND c.comment_date >= ?");
                    params.add(conditions.get("startDate"));
                }

                if (conditions.containsKey("endDate")) {
                    sql.append(" AND c.comment_date <= ?");
                    params.add(conditions.get("endDate"));
                }

                if (conditions.containsKey("authorEmail")) {
                    sql.append(" AND c.comment_author_email LIKE ?");
                    params.add("%" + conditions.get("authorEmail") + "%");
                }
            }

            sql.append(" ORDER BY c.comment_date_gmt DESC");

            return Db.find(sql.toString(), params.toArray());
        }, conditions != null ? conditions.size() : 0);
    }

    /**
     * 获取默认排序字段
     */
    @Override
    protected String getDefaultOrderBy() {
        return "comment_date_gmt DESC";
    }

    /**
     * 获取基本信息字段
     */
    @Override
    protected String getBasicFields() {
        return "comment_ID, comment_author, comment_author_email, comment_date, comment_content, comment_approved";
    }
}